﻿using CuaHangLego.Bussiness;
using CuaHangLego.Bussiness.Parser;
using CuaHangLego.Model;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CuaHangLego.DataAccess
{
    public class HoaDonMuaDA : DABase, IDA<HoaDonMua>
    {
        public static HoaDonMuaDA Instance = new HoaDonMuaDA();

        public int Insert(HoaDonMua item)
        {
            SqlCommand sql = new SqlCommand();
            sql.CommandText = string.Format("INSERT INTO HOADONMUA ({0},{1}) VALUES('{2}',{3}); SELECT CAST(scope_identity() AS int);",
                "ThoiGian", "NhaCungCapID",
                TConvert.CSDateTimeToSQL(item.ThoiGian), item.NhaCungCap.MaSo);
            OpenDB();
            int rs = int.Parse(ExecuteQuerySingleValue(sql).ToString());
            CloseDB();

            if (rs >= 0)
            {
                foreach (CTHD ct in item.DsCTHD)
                {
                    if (ct.SoLuong > 0) CTHDMuaDA.Instance.InsertToHoaDon(rs, ct);
                    SanPhamKhoDA.Instance.ChangeQuatity(ct.SanPham.MaSo, ct.SoLuong);
                    NhatKySanPhamDA.Instance.Insert(ct.SanPham.MaSo);
                }
            }

            return rs;
        }

        public bool Update(HoaDonMua item)
        {
            SqlCommand sql = new SqlCommand();
            sql.CommandText = string.Format("UPDATE HOADONMUA SET {0}={2},{1}={3} WHERE ID={4};",
                "ThoiGian", "NhaCungCapID",
                item.ThoiGian, item.NhaCungCap.MaSo, item.MaSo);
            OpenDB();
            int rs = ExecuteQuery(sql);
            CloseDB();
            // Get all CTHD to descrease number of SanPham
            HoaDonMua old = HoaDonMuaDA.Instance.GetByID(item.MaSo);
            foreach (CTHD ct in old.DsCTHD)
            {
                CTHDMuaDA.Instance.DeleteFromHD(item.MaSo, ct);
                SanPhamKhoDA.Instance.ChangeQuatity(ct.SanPham.MaSo, (-1) * ct.SoLuong);
                NhatKySanPhamDA.Instance.Insert(ct.SanPham.MaSo);
            }
            // Insert new CTHD list to HOADON
            foreach (CTHD ct in item.DsCTHD)
            {
                CTHDMuaDA.Instance.InsertToHoaDon(item.MaSo, ct);
                SanPhamKhoDA.Instance.ChangeQuatity(ct.SanPham.MaSo, ct.SoLuong);
                NhatKySanPhamDA.Instance.Insert(ct.SanPham.MaSo);
            }
            return rs > 0;
        }

        public bool Delete(HoaDonMua item)
        {
            SqlCommand sql = new SqlCommand();
            sql.CommandText = string.Format("DELETE FROM HOADONMUA WHERE {0}={1}", "ID", item.MaSo);
            OpenDB();
            int rs = int.Parse(ExecuteQuerySingleValue(sql).ToString());
            CloseDB();
            return rs > 0;
        }

        public IList<HoaDonMua> GetAll()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = string.Format("SELECT * FROM HOADONMUA");
            cmd.CommandType = System.Data.CommandType.Text;
            OpenDB();
            SqlDataReader reader = ExecuteQueryReader(cmd);
            IList<HoaDonMua> list = new List<HoaDonMua>();
            while (reader.Read())
            {
                list.Add(HoaDonMuaParser.Parse(reader));
            }
            CloseDB();
            
            // Don't run for lazy load
            
            //foreach (HoaDon hd in list)
            //{
            //    hd.DsCTHD = CTHDMuaDA.Instance.GetAllFromHD(hd.MaSo);
            //}

            return list;
        }

        public HoaDonMua GetByID(int ID)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = string.Format("SELECT * FROM HOADONMUA WHERE {0}={1}", "ID", ID);
            cmd.CommandType = System.Data.CommandType.Text;
            OpenDB();
            SqlDataReader reader = ExecuteQueryReader(cmd);
            HoaDonMua hd = null;
            while (reader.Read())
            {
                hd = HoaDonMuaParser.Parse(reader);
                break;
            }
            
            if (hd != null) hd.DsCTHD = CTHDMuaDA.Instance.GetAllFromHD(hd.MaSo);
            
            CloseDB();
            return hd;
        }

        public IList<HoaDonMua> GetAllByNhaCungCapID(int ID)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = string.Format("SELECT * FROM HOADONMUA WHERE {0}={1}", "NhaCungCapID", ID);
            cmd.CommandType = System.Data.CommandType.Text;
            OpenDB();
            SqlDataReader reader = ExecuteQueryReader(cmd);
            IList<HoaDonMua> list = new List<HoaDonMua>();
            while (reader.Read())
            {
                list.Add(HoaDonMuaParser.Parse(reader));
            }
            CloseDB();

            foreach (HoaDon hd in list)
            {
                hd.DsCTHD = CTHDMuaDA.Instance.GetAllFromHD(hd.MaSo);
            }

            return list;
        }
    }
}
